﻿@using System.Configuration
@using System.Data.SqlClient
@{
    if ((Request.QueryString.Count > 0) && (Request.QueryString["RPID"] != null) && (Request.QueryString["MM"] != null))
    {
        
        string reportingPeriodId = Request.QueryString["RPID"].Trim();
        string mm = Request.QueryString["MM"].Trim();
        DateTime mmDateTime = DateTime.ParseExact(mm, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);

        string fileName = string.Format("Export_To_SAP_RP{0}_{1}.csv", reportingPeriodId, mmDateTime.ToString("MMM"));

        System.Data.DataSet dsDownloadClaims = null;
        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
        {
            System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
            dsDownloadClaims = new System.Data.DataSet("Claims_DataSet");


            cmd.CommandText = "SELECT 'Export_To_SAP_' + CONVERT(VARCHAR,YEAR(EndDate)) + UPPER(DATENAME([MONTH], EndDate)) AS 'FileName', * FROM ReportingPeriod WHERE Id = @RPID;";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@RPID", reportingPeriodId);
            da.Fill(dsDownloadClaims, "ReportingPeriod");
            if (dsDownloadClaims.Tables["ReportingPeriod"].Rows.Count > 0)
            {
                fileName = string.Format("{0}_{1}_Claims.csv", dsDownloadClaims.Tables["ReportingPeriod"].Rows[0]["FileName"].ToString(), mmDateTime.ToString("MMM").ToUpper());
            }
            
            cmd.CommandText =
@"SELECT  B2E.NameLegal, 
        c.EntryOwner, 
        SUBSTRING(CONVERT(VARCHAR, c.ClaimDate, 111), 1, 7) AS 'OT_MONTH',
        CONVERT(DECIMAL(6,2),
        (ISNULL(SUM(CONVERT(INT, c.PH)), 0) * 8) +
        (ISNULL(SUM(CONVERT(INT, c.RD4)), 0) * 8) +
        (ISNULL(SUM(CONVERT(INT, c.RD8)), 0) * 16)) AS 'OT1_0',
        ISNULL(SUM(c.OT1_5), 0) AS 'OT1_5',
        ISNULL(SUM(c.OT2_0), 0) AS 'OT2_0',
        ISNULL(SUM(c.AlwShft), 0) AS 'AlwShft',
        ISNULL(SUM(c.AlwMeal), 0) AS 'AlwMeal'
FROM    ReportingPeriodClaimant rpc
LEFT JOIN 
        Claim c
        ON rpc.EntryOwner = c.EntryOwner
LEFT JOIN B2E
        ON c.EntryOwner = B2E.EmployeeMaster
WHERE   rpc.ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Id = @RPID)
        AND c.ClaimDate >= @SelectedMonth AND c.ClaimDate < DATEADD(MONTH, 1, @SelectedMonth) 
        AND c.Status = 6
        AND c.ApproveDate < (SELECT EndDate FROM ReportingPeriod WHERE Id = @RPID)
GROUP BY B2E.NameLegal, c.EntryOwner, SUBSTRING(CONVERT(VARCHAR, c.ClaimDate, 111), 1, 7);";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@SelectedMonth", mmDateTime.ToString("yyyy-MM-dd"));
            cmd.Parameters.AddWithValue("@RPID", reportingPeriodId);

            da.Fill(dsDownloadClaims, "Claims");
            System.Data.DataTable dt = dsDownloadClaims.Tables["Claims"];
            
            if (dt.Rows.Count > 0)
            {
                int rowCount = 0;

                // Write header
                sb.AppendLine("Name\tB2E_ID\tOT_MONTH\tOT1_0\tOT1_5\tOT2_0\tAlwShft\tAlwMeal");

                // Write body
                foreach (System.Data.DataRow row in dt.Rows)
                {
                    rowCount++;

                    foreach (System.Data.DataColumn column in dt.Columns)
                    {
                        sb.Append(row[column].ToString());
                        sb.Append('\t');
                    }
                    sb.Remove(sb.Length - 1, 1);

                    if (rowCount != dt.Rows.Count)
                    {
                        sb.AppendLine();
                    }
                }
            } // end if (dt.Rows.Count > 0)
            
        }

        using (MemoryStream ms = new MemoryStream())
        {
            using (System.IO.StreamWriter sw = new StreamWriter(ms, System.Text.Encoding.Unicode))
            {
                sw.AutoFlush = true;
                sw.Write(sb.ToString());
                sw.Close();
            }

            Response.Clear();
            Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename=\"{0}\"", fileName));
            Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
            Response.BinaryWrite(ms.ToArray());
            Response.End();
        }
    }
}